
Module modSupplier

    Private isError As Boolean

    ' Company Default Structure
    Public Structure Supplier
        Public OwnerEntityCode As String
        Public SupplierCode As String
        Public SupplierName As String
        Public ContactPerson As String
        Public Address1 As String
        Public Address2 As String
        Public Address3 As String
        Public Address4 As String
        Public BillToAddress As String
        Public Country As String
        Public ZipCode As String
        Public Phone1 As String
        Public Fax As String
        Public EmailAddress As String
        Public Website As String
        Public TaxRegistration As String
        Public PaymentTerm As String
        Public APControlAccount As String
        Public TaxCode As String
        Public CreditLimit As Double
        Public Discount As Double
        Public PaymentMethod As String
        Public CurrencyCode As String
        Public Remarks As String

    End Structure

    Public oSupDef As Supplier
    Private InputFolderPath As String = p_oCompDef.sInboxDir

    Public Function UploadSUPPLIER(ByVal sSubFolder As String, ByRef sErrdesc As String) As Long

        'Event      :   UploadSUPPLIER()
        'Purpose    :   For Checking Errors & updation of data in SAP from Customer CSV Files
        'Author     :   Sri
        'Date       :   03 DEC 2013   

        Dim sFuncName As String = String.Empty
        Dim BPFileExist As Boolean
        Dim sFileType As String = String.Empty

        Try
            sFuncName = "UploadSUPPLIER"

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)
            BPFileExist = False

            'CMS
            Dim DirInfo As New System.IO.DirectoryInfo(p_oCompDef.sInboxDir & "\" & sSubFolder)
            Dim files() As System.IO.FileInfo

            files = DirInfo.GetFiles("*SUPPLIER*.csv")
            For Each File As System.IO.FileInfo In files
                BPFileExist = True
                isError = False
                sFileType = Replace(File.Name, ".csv", "").Trim
                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling readCSVFileSupplier for " & File.FullName & " to check error in CSV file", sFuncName)
                readCSVFileSupplier(File.FullName, File.Name)
                If isError = True Then
                    sErrdesc = "Wrong data in CSV file.::" & File.Name & " Please refer to error log fo more details."
                    If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug(sErrdesc, sFuncName)
                    Call WriteToLogFile(sErrdesc, sFuncName)
                    AddDataToTable(p_oDtError, File.Name, "Error", sErrdesc)
                    FileMoveToArchive(File, File.FullName, RTN_ERROR)
                    If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Error in updation. RollBack executed for " & File.FullName, sFuncName)
                    Throw New ArgumentException(sErrdesc)
                Else
                    If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling AddUpdateSupplier() for " & File.FullName & " to update data in database", sFuncName)
                    If AddUpdateSupplier(File.FullName, sErrdesc) <> RTN_SUCCESS Then
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling FileMoveToArchive for moving CSV file to archive folder", sFuncName)
                        AddDataToTable(p_oDtError, File.Name, "Error", sErrdesc)
                        FileMoveToArchive(File, File.FullName, RTN_ERROR)
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Error in updation. RollBack executed for " & File.FullName, sFuncName)
                    Else
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Data updated successfully for " & File.FullName, sFuncName)
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling FileMoveToArchive for moving CSV file to archive folder", sFuncName)
                        FileMoveToArchive(File, File.FullName, RTN_SUCCESS)
                    End If
                End If
            Next

            UploadSUPPLIER = RTN_SUCCESS
            If BPFileExist = False Then If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("No Business Partner CSV available for updation", sFuncName)
        Catch ex As Exception
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error in Uploading BP", sFuncName)
            Call WriteToLogFile(ex.Message, sFuncName)
        End Try
    End Function

    Private Sub readCSVFileSupplier(ByVal CurrFileToUpload As String, ByVal sFileName As String)

        'Event      :   readCSVFileSupplier
        'Purpose    :   For reading of Business Partner CSV file
        'Author     :   Sri 
        'Date       :   20 NOV 2013 

        Dim sFuncName As String = ""
        Dim sSQL As String = String.Empty
        Dim dt As DataTable

        Try
            sFuncName = "readCSVFileSupplier"
            Dim dv As DataView = GetDataViewFromCSV(CurrFileToUpload)

            sSQL = "SELECT ""Code"" FROM ""@AI_TB01_COMPANYDATA"""
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
            dt = ExecuteSQLQueryForDT(sSQL)

            For i As Integer = 0 To dv.Count - 1
                With oSupDef
                    .OwnerEntityCode = dv(i)(0).ToString
                    .SupplierCode = dv(i)(1).ToString
                    .SupplierName = dv(i)(2).ToString
                    .ContactPerson = dv(i)(3).ToString
                    .Address1 = dv(i)(4).ToString
                    .Address2 = dv(i)(5).ToString
                    .Address3 = dv(i)(6).ToString
                    .Address4 = dv(i)(7).ToString
                    .Country = dv(i)(8).ToString
                    .ZipCode = dv(i)(9).ToString
                    .Phone1 = dv(i)(10).ToString
                    .Fax = dv(i)(11).ToString
                    .EmailAddress = dv(i)(12).ToString
                    .Website = dv(i)(13).ToString
                    .TaxRegistration = dv(i)(14).ToString
                    .PaymentTerm = dv(i)(15).ToString
                    .APControlAccount = dv(i)(16).ToString
                    .TaxCode = dv(i)(17).ToString
                    .CreditLimit = CDbl(dv(i)(18))
                    .Discount = CDbl(dv(i)(19))
                    .PaymentMethod = dv(i)(20).ToString
                    .CurrencyCode = dv(i)(21).ToString

                    If Not IsNothing(dv(i)(22).ToString) Then
                        .Remarks = dv(i)(22).ToString
                    Else
                        .Remarks = String.Empty
                    End If

                    If .OwnerEntityCode.Length = 0 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Owner entity Code is mandatory", sFuncName)
                    End If

                    If .OwnerEntityCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Owner entity Code Length is greater then 10", sFuncName)
                    End If

                    dt.DefaultView.RowFilter = "Code = '" & dv(i)(0).ToString & "'"
                    If dt.DefaultView.Count = 0 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Owner enttiy code ::" & dv(i)(0).ToString & " does not exist in SAP [@AI_TB01_COMPANYDATA] table.", sFuncName)
                    End If

                    If .SupplierCode.Length = 0 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Supplier Code is mandatory", sFuncName)
                    End If

                    If .SupplierCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Supplier Code Length is not greater then 15", sFuncName)
                    End If
                    If .SupplierName.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Supplier Name Length is not greater then 80", sFuncName)
                    End If
                    If .ContactPerson.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Contact Person Length is not greater then 80", sFuncName)
                    End If
                    If .Address1.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Address1 Length is not greater then 80", sFuncName)
                    End If
                    If .Address2.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Address2 Length is not greater then 80", sFuncName)
                    End If
                    If .Address3.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Address3 Length is not greater then 80", sFuncName)
                    End If
                    If .Address4.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Address4 Length is not greater then 80", sFuncName)
                    End If
                    If .Country.Length > 30 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Country Length is not greater then 30", sFuncName)
                    End If
                    If .ZipCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Postal Code Length is not greater then 10", sFuncName)
                    End If
                    If .Phone1.Length > 20 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Phone Length is not greater then 15", sFuncName)
                    End If
                    If .Fax.Length > 20 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Fax Length is not greater then 15", sFuncName)
                    End If
                    If .EmailAddress.Length > 100 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". EmailAddress Length is not greater then 100", sFuncName)
                    End If
                    If .Website.Length > 100 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Website Length is not greater then 100", sFuncName)
                    End If
                    If .TaxRegistration.Length > 30 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". TaxRegistration Length is not greater then 30", sFuncName)
                    End If
                    If .PaymentTerm.Length > 50 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". PaymentTerm Length is not greater then 50", sFuncName)
                    End If
                    If .APControlAccount.Length > 15 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". AP Control Account Length is not greater then 15", sFuncName)
                    End If
                    If .TaxCode.Length > 6 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". TaxCode Length is not greater then 6", sFuncName)
                    End If
                    If .PaymentMethod.Length > 50 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Payment Method Length is not greater then 50", sFuncName)
                    End If

                    If .CurrencyCode.Length > 3 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Currency Code Length is not greater then 3", sFuncName)
                    End If

                    If .Remarks.Length > 255 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Remarks Length is not greater then 255", sFuncName)
                    End If

                End With

            Next
        Catch ex As Exception
            isError = True
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error occured while reading content of  " & ex.Message, sFuncName)
            Call WriteToLogFile(ex.Message, sFuncName)
        End Try
    End Sub

    Private Function AddUpdateSupplier(ByVal CurrFileToUpload As String, ByRef sErrdesc As String) As Long

        'Function   :   AddUpdateSupplier()
        'Purpose    :   For Adding new Business Partner
        '           :   iCardType (0 : Customer 1:Vendor)
        'Author     :   Sri
        'Date       :   03 Dec 2013       

        Dim iErr As Integer = 0
        Dim iRet As Integer
        Dim sErr As String = ""
        Dim sql As String = ""

        Dim sFuncName As String = "AddUpdateSupplier"
        ' Declare variable
        Dim oBP As SAPbobsCOM.BusinessPartners
        Dim oRs As SAPbobsCOM.Recordset
        Dim oDIComp() As SAPbobsCOM.Company
        Dim oDtDistinct As DataTable
        Dim dtPT As DataTable
        Dim dtAC As DataTable

        Try

            oCustDef = Nothing

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("calling GetDataViewFromCSV()", sFuncName)
            Dim dv As DataView = GetDataViewFromCSV(CurrFileToUpload)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("getting distinct DBs in the file.", sFuncName)
            oDtDistinct = dv.Table.DefaultView.ToTable(True, "F1")

            ReDim oDIComp(oDtDistinct.Rows.Count)

            With oSupDef

                For j As Integer = 0 To oDtDistinct.Rows.Count - 1
                    oDIComp(j) = New SAPbobsCOM.Company

                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling ConnectToTargetCompany()", sFuncName)
                    If ConnectToTargetCompany(oDIComp(j), oDtDistinct.Rows(j).Item(0).ToString, sErrdesc) <> RTN_SUCCESS Then
                        Throw New ArgumentException(sErrdesc)
                    End If

                    ' Get "business object" from Company object
                    oBP = oDIComp(j).GetBusinessObject(SAPbobsCOM.BoObjectTypes.oBusinessPartners)
                    oRs = oDIComp(j).GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting transaction on company database " & oDIComp(j).CompanyDB, sFuncName)
                    oDIComp(j).StartTransaction()

                    Dim DBRows() As DataRow = dv.Table.Select("F1='" & oDtDistinct.Rows(j).Item(0).ToString & "'")

                    For Each row As DataRow In DBRows
                        .SupplierCode = row.Item(1).ToString

                        sql = "select * from ocrd where ""CardType""='S' and ""CardCode""='" & .SupplierCode & "'"
                        oRs.DoQuery(sql)

                        If oRs.RecordCount > 0 Then
                            oBP.GetByKey(.SupplierCode)
                        End If

                        .SupplierName = row.Item(2).ToString
                        .ContactPerson = row.Item(3).ToString
                        .Address1 = row.Item(4).ToString
                        .Address2 = row.Item(5).ToString
                        .Address3 = row.Item(6).ToString
                        .Address4 = row.Item(7).ToString
                        .Country = row.Item(8).ToString
                        .ZipCode = row.Item(9).ToString
                        .Phone1 = row.Item(10).ToString
                        .Fax = row.Item(11).ToString
                        .EmailAddress = row.Item(12).ToString
                        .Website = row.Item(13).ToString
                        .TaxRegistration = row.Item(14).ToString
                        .PaymentTerm = row.Item(15).ToString
                        .APControlAccount = row.Item(16).ToString
                        Select Case row.Item(17).ToString
                            Case "GSTZR"
                                .TaxCode = "ZI"
                            Case "GSTNA"
                                .TaxCode = "NI"
                            Case "GSTIN"
                                .TaxCode = "SI"
                            Case Else
                                sErrdesc = "Invalid TaxCode found " & row.Item(17).ToString
                        End Select

                        .CreditLimit = CDbl(row.Item(18))
                        .Discount = CDbl(row.Item(19))

                        Select Case row.Item(20).ToString
                            Case Microsoft.VisualBasic.UCase("CHEQUE")
                                .PaymentMethod = "Check"
                            Case Else
                                .PaymentMethod = row.Item(20).ToString
                        End Select

                        '.PaymentMethod = row.Item(20).ToString
                        .CurrencyCode = row.Item(21).ToString
                        .Remarks = row.Item(22).ToString
                        .BillToAddress = "BillToAddress1"

                        ' validations
                        sql = "select ""PymntGroup"" from OCTG"
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sql, sFuncName)
                        dtPT = ExecuteSQLQueryForDT(sql, oDIComp(j).CompanyDB)

                        sql = "SELECT ""AcctCode"" FROM OACT"
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sql, sFuncName)
                        dtAC = ExecuteSQLQueryForDT(sql, oDIComp(j).CompanyDB)

                        dtAC.DefaultView.RowFilter = "AcctCode = '" & .APControlAccount & "'"
                        If dtAC.DefaultView.Count = 0 Then
                            isError = True
                            Call WriteToLogFile("AP Controla Account ::" & .APControlAccount & " does not exist in SAP.", sFuncName)
                        End If

                        dtPT.DefaultView.RowFilter = "PymntGroup = '" & .PaymentTerm & "'"
                        If dtPT.DefaultView.Count = 0 Then
                            isError = True
                            Call WriteToLogFile("Payment group ::" & .PaymentTerm & " does not exist in SAP.", sFuncName)
                        End If


                        oBP.CardCode = .SupplierCode
                        oBP.CardName = .SupplierName
                        oBP.Phone1 = .Phone1
                        oBP.Fax = .Fax
                        oBP.EmailAddress = .EmailAddress
                        oBP.Website = .Website
                        'oBP.TaxExemptionLetterNum = .TaxRegistration
                        oBP.FederalTaxID = .TaxRegistration
                        oBP.DebitorAccount = .APControlAccount
                        oBP.VatGroup = .TaxCode
                        oBP.CreditLimit = .CreditLimit
                        oBP.DiscountPercent = .Discount
                        oBP.Currency = .CurrencyCode
                        oBP.FreeText = .Remarks
                        oBP.PeymentMethodCode = .PaymentMethod

                        'Bill to Address
                        Dim oRecordSetLoc As SAPbobsCOM.Recordset
                        oRecordSetLoc = (oDIComp(j).GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset))
                        sql = "select ""CardCode"",""AdresType"",""LineNum"" from CRD1 where ""CardCode""='" & .SupplierCode & "' "
                        sql &= " and ""AdresType""='B'"
                        sql &= " and ""Address""='" & .BillToAddress & "'"
                        oRecordSetLoc.DoQuery(sql)

                        If oRecordSetLoc.EoF Then
                            oBP.Addresses.Add()
                            oBP.Addresses.SetCurrentLine(0)
                        Else
                            For a As Integer = 0 To oBP.Addresses.Count - 1
                                oBP.Addresses.SetCurrentLine(a)
                                If oBP.Addresses.AddressName = .BillToAddress And oBP.Addresses.AddressType = SAPbobsCOM.BoAddressType.bo_BillTo Then
                                    Exit For
                                End If
                            Next
                        End If

                        oBP.Addresses.AddressType = SAPbobsCOM.BoAddressType.bo_BillTo
                        oBP.Addresses.AddressName = .BillToAddress
                        oBP.Addresses.Street = .Address1
                        oBP.Addresses.Block = .Address3
                        oBP.Addresses.City = .Address4
                        oBP.Addresses.StreetNo = .Address2
                        oBP.Addresses.ZipCode = .ZipCode
                        If Not GetCountryCode(oDIComp(j), .Country) = String.Empty Then
                            oBP.Addresses.Country = GetCountryCode(oDIComp(j), .Country)
                        End If

                        If oRs.RecordCount > 0 Then
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("BP Code : " & .SupplierCode & " already exist. Call Update", sFuncName)
                            iRet = oBP.Update()
                        Else
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Call Add to insert BP Code : " & .SupplierCode, sFuncName)
                            oBP.CardType = SAPbobsCOM.BoCardTypes.cSupplier
                            Dim iCnt As Integer = 0
                            oRs.DoQuery("SELECT ""PayMethCod"" FROM OPYM WHERE ""Type"" IN('O')")
                            If oRs.RecordCount > 0 Then
                                oRs.MoveFirst()
                                While oRs.EoF = False
                                    iCnt += 1
                                    oBP.BPPaymentMethods.PaymentMethodCode = oRs.Fields.Item("PayMethCod").Value
                                    oBP.BPPaymentMethods.SetCurrentLine(iCnt - 1)
                                    oBP.BPPaymentMethods.Add()
                                    oRs.MoveNext()
                                End While
                            End If
                            oBP.DebitorAccount = "2-12100-00"
                            iRet = oBP.Add()
                        End If

                        If iRet <> 0 Then
                            isError = True
                            oDIComp(j).GetLastError(iErr, sErrdesc)
                            Call WriteToLogFile("Error in addition/updation for BP Code : " & .SupplierCode & "---" & sErr, sFuncName)
                            Exit Function
                        End If
                    Next
                Next

            End With
            For lCounter As Integer = 0 To UBound(oDIComp)
                If Not oDIComp(lCounter) Is Nothing Then
                    If oDIComp(lCounter).Connected = True Then
                        If oDIComp(lCounter).InTransaction = True Then
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Commit transaction on company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                            oDIComp(lCounter).EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
                        End If
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Disconnecting company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                        oDIComp(lCounter).Disconnect()
                        oDIComp(lCounter) = Nothing
                    End If
                End If
            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Function completed successfully.", sFuncName)
            AddUpdateSupplier = RTN_SUCCESS

        Catch ex As Exception
            isError = True
            sErrdesc = ex.Message
            If iRet = 0 Then p_oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error occured while updating the record", sFuncName)
            Call WriteToLogFile(ex.Message, sFuncName)
        End Try
    End Function

End Module
